# -*- coding: utf8 -*-

from project.sqlite3_db.table import Table

class TableFile2file( Table ):

    _table_name = "file2file"

    def create( self ):
        cur = self.getCursor()

        # Create table
        cur.execute(
            "CREATE TABLE IF NOT EXISTS file2file ("+
            "id INTEGER PRIMARY KEY, "+
            "file1_id INTEGER, "+
            "file2_id INTEGER, "+
            "file1_count INTEGER, "+
            "file2_count INTEGER, "+
            "equal_images INTEGER, "+
            "quality_points INTEGER)"
        )
        cur.execute(
            "CREATE INDEX IF NOT EXISTS files ON file2file (file1_id, file2_id)"
        )
        cur.execute(
            "CREATE INDEX IF NOT EXISTS files_count ON file2file (file1_count, file2_count, equal_images, quality_points)"
        )
        cur.close()
        return


    def insert_file2file( self, file1_id, file2_id, file1_count, file2_count):
        cur = self.getCursor()

        cur.execute(
            "INSERT INTO file2file ("+
            "file1_id, file2_id, file1_count, file2_count, equal_images, quality_points"+
            ") VALUES ("+
            ":file1_id, :file2_id, :file1_count, :file2_count, :equal_images, :quality_points"+
            ")", {
                "file1_id":file1_id,
                "file2_id":file2_id,
                "file1_count":file1_count,
                "file2_count":file2_count,
                "equal_images":1,
                "quality_points":0
            }
        )
        self.getConnection().commit()
        last_id = cur.lastrowid
        cur.close()
        return last_id

    def has_file2file(self, file1_id, file2_id):
        cur = self.getCursor()
        cur.execute(
            "SELECT * FROM file2file WHERE file1_id=:id1 AND file2_id=:id2",
            {"id1": file1_id, "id2":file2_id}
        )
        item = cur.fetchone()
        if not item:
            cur.execute(
                "SELECT * FROM file2file WHERE file1_id=:id2 AND file2_id=:id1",
                    {"id1": file1_id, "id2":file2_id}
            )
            item = cur.fetchone()
            if not item:
                item = False
            else:
                item["reverse"] = True
        else:
            item["reverse"] = False

        cur.close()
        return item

    def remove_file_file2file(self, file_id):
        cur = self.getCursor()
        cur.execute(
            "DELETE FROM file2file WHERE file1_id=:id OR file2_id=:id",
                {"id": file_id}
        )
        self.getConnection().commit()
        rc = cur.rowcount
        cur.close()
        return rc

    def inc_file2file( self, f2f_id, quality):
        cur = self.getCursor()
        cur.execute(
            "UPDATE file2file SET equal_images=equal_images+1, quality_points=quality_points+:quality "+
            "WHERE id=:id",
            {"id":f2f_id, "quality":quality}
        )
        self.getConnection().commit()
        rc = cur.rowcount
        cur.close()
        return rc

    def get_equal_files(self, diff):
        cur = self.getCursor()
        cur.execute(
            "SELECT * FROM file2file WHERE "+
            "( file1_count>:diff AND ABS(file1_count-equal_images)<:diff) OR ( file2_count>:diff AND ABS(file2_count-equal_images)<:diff",
                {"diff": diff}
        )
        item = cur.fetchone()
        cur.close()
        if not item:
            item = False
        return item

    def get_count(self):
        cur = self.getCursor()
        cur.execute("SELECT COUNT(1) as c FROM file2file")
        item = cur.fetchone()
        cur.close()
        if not item:
            item = 0
        return item['c']

    def get_file2file(self, count, offset):
        cur = self.getCursor()
        cur.execute("SELECT * FROM file2file LIMIT :limit OFFSET :offset",
                {"limit":count,"offset":offset})
        items = cur.fetchall()
        cur.close()
        return items

    pass
